package function;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import entity.Reader;
import interfaces.ReaderDao;

import java.sql.Connection;
import java.util.List;

public class ReaderDaoImpl extends BaseDao<Reader> implements ReaderDao {

    private static function.ReaderDaoImpl readerDao = null;

    private ReaderDaoImpl() {
    }

    public static function.ReaderDaoImpl getReaderDao() {
        if (readerDao == null) {
            readerDao = new function.ReaderDaoImpl();
        }
        return readerDao;
    }

    @Override
    public void insert(Connection conn, Reader reader) {
        String sql = "INSERT INTO reader(reader_id,type,name,age,sex,phone,dept,regdate)VALUES(?,?,?,?,?,?,?,?)";
        try {
            update(conn, sql, reader.getReaderid(), reader.getType(),reader.getName(),
                    reader.getAge(), reader.getSex(), reader.getPhone(), reader.getDept(), reader.getRegdate());
            System.out.println("添加成功");
        } catch (MySQLIntegrityConstraintViolationException throwables) {
            System.out.println("读者类型名字不能重复,添加失败");
        }
    }

    @Override
    public void deleteById(Connection conn, String id) {
        String sql = "delete from reader where reader_id=?";
        try {
            update(conn, sql, id);
        } catch (MySQLIntegrityConstraintViolationException throwables) {
            System.out.println("该读者已被征用，无法删除");
        }
    }

    @Override
    public void update(Connection conn, Reader reader) {
        //String sql1 = "UPDATE reader SET type=?,name=?,age=?,sex=?,phone=?dept=?regdate=? WHERE reader_id=?;";
        String sql = "UPDATE reader SET `type`=?,`name`=?,age=?,sex=?,phone=?,dept=?,regdate=? WHERE reader_id=?;";
        try {
            update(conn, sql, reader.getType(), reader.getName(), reader.getAge(), reader.getSex(), reader.getPhone(), reader.getDept(), reader.getRegdate(),reader.getReaderid());
            System.out.println("修改成功");
            } catch (MySQLIntegrityConstraintViolationException throwables) {
            System.out.println("修改名字不能相同，修改失败");
        }
    }

    @Override
    public  Reader getReaderById(Connection conn, String id) {
        String sql = "SELECT r.reader_id readerid,r.type,rt.type_name typeName,r.name,r.age,r.sex,r.phone,r.dept,r.regdate FROM reader r JOIN reader_type rt ON r.type=rt.id WHERE r.reader_id=?";
        Reader reader = getInstance(conn, sql, id);
        return reader;
    }

    public Reader getReaderByType(Connection conn, int id) {
        String sql = "SELECT r.reader_id readerid,r.type,rt.type_name typeName,r.name,r.age,r.sex,r.phone,r.dept,r.regdate FROM reader r JOIN reader_type rt ON r.type=rt.id WHERE r.type=?";
        Reader reader = getInstance(conn, sql, id);
        return reader;
    }
    @Override
    public List<Reader> getAll(Connection coon) {
        //String sql = "select reader_id readerid,type type,name name,age age,sex sex,phone phone,dept dept,regdate regdate from reader";
        String sql = "SELECT r.reader_id readerid,r.type,rt.type_name typeName,r.name,r.age,r.sex,r.phone,r.dept,r.regdate FROM reader r JOIN reader_type rt ON r.type=rt.id";
        List<Reader> list = getForList(coon, sql);
        return list;
    }
    public Long getCount(Connection conn) {
        String sql="select count(*)from reader";
        return getValue(conn,sql);
    }


}


